{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Distribution Formula Demo","Code Outline"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Distribution Formula Demo","index":0,"n_rows":28,"n_cols":20,"rows_kept":28,"cut":false,"hidden":false,"merged":[[4,6,7,16]]}
[]
[]
[null,"Column1","Column2","Column3","Column4",null,"=DISTRIBUTION(Range, ColumnNumber)"]
[null,1,5,10,8]
[null,2,3,9,8,null,"    I created the DISTRIBUTION formula in order to practice creating a custom spilling formula in visual basic.  Unlike other formulas, spilling formulas must create a two-dimensional array result.  The DISTRIBUTION formula is intended to collect data and then determine how many times each result appears in the data.  The logical steps in the program are:"]
[null,3,2,8,8]
[null,4,1,7,8]
[null,null,2,"Hello",8]
[null,3,3,5,8,null,1,"Collects data from a single column in a range"]
[null,2,5,4,8,null,2,"Creates a list of all values that appear in that column and counts how many times each appears"]
[null,1,6,3,8,null,3,"Sorts the values(and counts) by count in descending order"]
[null,2,2,2,8,null,4,"Creates a new array with the results and returns the result"]
[null,3,3,1,8]
[null,5,1,"Hello",8]
[null,6,9,1,8,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"This column doesn't exist and does not return results"]
[null,null,9,2,8,null,"Column #:",1,null,"Column #:",2,null,"Column #:",3,null,"Column #:",4,null,"Column #:",6]
[null,3,9,3,8]
[null,1,9,4,8,null,{"$f":"Distribution($B$4:$E$18,H16)","v":"VALUES"},"COUNT",null,{"$f":"Distribution($B$4:$E$18,K16)","v":"VALUES"},"COUNT",null,{"$f":"Distribution($B$4:$E$18,N16)","v":"VALUES"},"COUNT",null,{"$f":"Distribution($B$4:$E$18,Q16)","v":"VALUES"},"COUNT",null,{"$f":"Distribution($B$4:$E$18,T16)","v":"VALUES"},"COUNT"]
[null,null,null,null,null,null,3,4,null,9,4,null,"Hello",2,null,8,15]
[null,null,null,null,null,null,1,3,null,3,3,null,4,2]
[null,null,null,null,null,null,2,3,null,2,3,null,3,2]
[null,null,null,null,null,null,4,1,null,5,2,null,2,2]
[null,null,null,null,null,null,5,1,null,1,2,null,1,2]
[null,null,null,null,null,null,6,1,null,6,1,null,10,1]
[null,null,null,null,null,null,null,null,null,null,null,null,9,1]
[null,null,null,null,null,null,null,null,null,null,null,null,8,1]
[null,null,null,null,null,null,null,null,null,null,null,null,7,1]
[null,null,null,null,null,null,null,null,null,null,null,null,5,1]

{"sheet":"Code Outline","index":1,"n_rows":23,"n_cols":10,"rows_kept":23,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[null,"Code Outline"]
[null,null,"Declare variables"]
[null,null,null,"TableValues array"]
[null,null,null,"TableCounts array"]
[]
[null,null,"If Statement to test column number validity"]
[]
[null,null,null,"Iterate through the cells in the column",null,null,null,null,null,"0-index in the arrays is not part of our results."]
[null,null,null,null,"Get the value of the current cell"]
[null,null,null,null,"Try to match the current value to the value array"]
[null,null,null,null,"If it matches:  add 1 to the count in the TableCounts element that matches"]
[null,null,null,null,"If it doesn't match:  Increase the size of the 2 arrays.  Put the value in the value array.  Set the count array to 1."]
[]
[]
[null,null,null,"Sort and transfer the results"]
[]
[null,null,null,null,"Iterate through the ResultValues array (starting at 1) finding the value with the most occurences each time.."]
[null,null,null,null,null,"Iterate through the TableCounts array to find the value with the highest count"]
[null,null,null,null,null,null,"Transfer the value and the count to ResultValues array starting with zero.  Track the values that have been transferred with the Migrated array"]